package com.cn.jdia.springmvc.handlers;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import org.joda.time.DateTime;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.google.gson.Gson;

@Controller
public class TansportController {

	@RequestMapping("/transportctrl")
	public String insertTransportData(HttpServletRequest request, HttpServletResponse response) throws IOException {
		response.setContentType("text/html;charset=utf-8");
		PrintWriter out = response.getWriter();

		String prodate = request.getParameter("prodate");
		String chugang = request.getParameter("chugang");
		String kzl = request.getParameter("kzl");
		String jingang = request.getParameter("jingang");
		String guozhan = request.getParameter("guozhan");
		String shangyong = request.getParameter("shangyong");
		String yaoke = request.getParameter("yaoke");
		String huowu = request.getParameter("huowu");
		String xingli = request.getParameter("xingli");
		String youjian = request.getParameter("youjian");
		String teshu = request.getParameter("teshu");
		String person = request.getParameter("person");

		System.out.println(prodate + " , " + chugang + " , " + kzl);
		System.out.println(jingang + " , " + guozhan + " , " + shangyong);
		System.out.println(yaoke + " , " + huowu + " , " + xingli);
		System.out.println(youjian + " , " + teshu + " , " + person);

		DateTime datetime = new DateTime();
		String nowDate = datetime.toString("YYYY-MM-dd HH:mm:ss");

		@SuppressWarnings("resource")
		ClassPathXmlApplicationContext w_context = new ClassPathXmlApplicationContext("springmvc.xml");
		DataSource ds = (DataSource) w_context.getBean("dataSource2");

		int ret = -1;

		// select before insert
		StringBuffer check = new StringBuffer("SELECT * from YUNSHU WHERE 日期= TO_DATE('").append(prodate)
				.append(" 00:00:00', 'yyyy-mm-dd hh24:mi:ss')");
		try {
			Connection conn = ds.getConnection();
			Statement st = conn.createStatement();
			ResultSet rs = st.executeQuery(check.toString());

			int count = 0;
			while (rs.next()) {
				count++;
			}
			rs.close();

			System.out.println(count);

			// delete
			if (count != 0) {
				StringBuffer del = new StringBuffer("DELETE FROM YUNSHU WHERE 日期= TO_DATE('").append(prodate)
						.append(" 00:00:00', 'yyyy-mm-dd hh24:mi:ss')");
				st.executeUpdate(del.toString());
			}

			// insert
			StringBuffer sb = new StringBuffer("INSERT INTO YUNSHU VALUES (TO_DATE('").append(prodate)
					.append(" 00:00:00', 'yyyy-mm-dd hh24:mi:ss'),").append(chugang).append(",").append(kzl).append(",")
					.append(jingang).append(",").append(guozhan).append(",").append(shangyong).append(",").append(yaoke)
					.append(",");

			if ((teshu == null) || ("".equals(teshu))) {
				sb.append("'无特殊事件',");
			} else {
				sb.append("'").append(teshu).append("',");
			}

			if ((person == null) || "".equals(person)) {
				sb.append("'',");
			} else {
				sb.append("'").append(person).append("',");
			}

			sb.append("TO_DATE('").append(nowDate).append("','yyyy-mm-dd hh24:mi:ss'),").append(huowu).append(",")
					.append(xingli).append(",").append(youjian).append(")");

			ret = st.executeUpdate(sb.toString());

			st.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

		}

		out.write(ret + "");
		out.flush();
		out.close();
		return null;
	}

	@RequestMapping("/updateTransport")
	@ResponseBody
	public String updateTransport(HttpServletRequest request, HttpServletResponse response) throws IOException {
		System.out.println("update transport info");

		response.setContentType("text/html;charset=utf-8");
		String json = request.getParameter("strJson");
		System.out.println(json);

		Gson gson = new Gson();
		@SuppressWarnings("unchecked")
		Map<String, String> map = gson.fromJson(json, HashMap.class);
		// System.out.println(map);

		String date = map.get("Date");
		String chugang = map.get("chugang");
		String jingang = map.get("jingang");
		String guozhan = map.get("guozhan");
		String kzl = map.get("kzl").replaceAll("%", "");
		String shangyong = map.get("shangyong");
		String yaoke = map.get("yaoke");
		String huowu = map.get("huowu");
		String xingli = map.get("xingli");
		String youjian = map.get("youjian");
		String teshu = map.get("teshu");

		@SuppressWarnings("resource")
		ClassPathXmlApplicationContext w_context = new ClassPathXmlApplicationContext("springmvc.xml");
		DataSource ds = (DataSource) w_context.getBean("dataSource2");

		int ret=-1;
		try {
			Connection conn = ds.getConnection();
			Statement st = conn.createStatement();

			StringBuffer sb = new StringBuffer("UPDATE YUNSHU SET 出港旅客=").append(chugang).append(",客座率=").append(kzl)
					.append(",进港旅客=").append(jingang).append(",过站旅客=").append(guozhan).append(",商用座位=")
					.append(shangyong).append(",要客保障=").append(yaoke).append(",货物=").append(huowu).append(",行李=")
					.append(xingli).append(",邮件=").append(youjian).append(",特殊事件='").append(teshu)
					.append("' where 日期=TO_DATE('").append(date).append(" 00:00:00','yyyy-mm-dd hh24:mi:ss')");
			
			System.out.println(sb.toString());
			
			ret = st.executeUpdate(sb.toString());

			st.close();
			conn.close();
		} catch (Exception e) {
			e.printStackTrace();
		}
		System.out.println("ret:"+ret);
		
		return "0";
	}

}
